
import warnings
warnings.filterwarnings('ignore')

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns


# reading the application_data.csv file to dataframe using read_csv:

app_df=pd.read_csv('application_data.csv')
app_df


# reading the previous_application.csv file to dataframe using read_csv:

prev_df = pd.read_csv('previous_application.csv')
prev_df


# Top 5 rows:

app_df.head()

# The header row looks fine


# Last 5 rows:

app_df.tail()

# The bοttοm rοws lοοks fine.
# There are nο junk values like page number, NaN values in bοttοm mοst rοw


# Shape of dataframe:

app_df.shape   # app_df has 307511 rows and 122 columns

(307511, 122)


#  Info of the dataframe:

app_df.info(null_counts=True, verbose=True)  # like we can see there is 122 columns

# There are a lοt οf cοlumns with null values but datatypes οf these cοlumns lοοks fine

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 307511 entries, 0 to 307510
Data columns (total 122 columns):
 #    Column                        Non-Null Count   Dtype  
---   ------                        --------------   -----  
 0    SK_ID_CURR                    307511 non-null  int64  
 1    TARGET                        307511 non-null  int64  
 2    NAME_CONTRACT_TYPE            307511 non-null  object 
 3    CODE_GENDER                   307511 non-null  object 
 4    FLAG_OWN_CAR                  307511 non-null  object 
 5    FLAG_OWN_REALTY               307511 non-null  object 
 6    CNT_CHILDREN                  307511 non-null  int64  
 7    AMT_INCOME_TOTAL              307511 non-null  float64
 8    AMT_CREDIT                    307511 non-null  float64
 9    AMT_ANNUITY                   307499 non-null  float64
 10   AMT_GOODS_PRICE               307233 non-null  float64
 11   NAME_TYPE_SUITE               306219 non-null  object 
 12   NAME_INCOME_TYPE              307511 non-null  object 
 13   NAME_EDUCATION_TYPE           307511 non-null  object 
 14   NAME_FAMILY_STATUS            307511 non-null  object 
 15   NAME_HOUSING_TYPE             307511 non-null  object 
 16   REGION_POPULATION_RELATIVE    307511 non-null  float64
 17   DAYS_BIRTH                    307511 non-null  int64  
 18   DAYS_EMPLOYED                 307511 non-null  int64  
 19   DAYS_REGISTRATION             307511 non-null  float64
 20   DAYS_ID_PUBLISH               307511 non-null  int64  
 21   OWN_CAR_AGE                   104582 non-null  float64
 22   FLAG_MOBIL                    307511 non-null  int64  
 23   FLAG_EMP_PHONE                307511 non-null  int64  
 24   FLAG_WORK_PHONE               307511 non-null  int64  
 25   FLAG_CONT_MOBILE              307511 non-null  int64  
 26   FLAG_PHONE                    307511 non-null  int64  
 27   FLAG_EMAIL                    307511 non-null  int64  
 28   OCCUPATION_TYPE               211120 non-null  object 
 29   CNT_FAM_MEMBERS               307509 non-null  float64
 30   REGION_RATING_CLIENT          307511 non-null  int64  
 31   REGION_RATING_CLIENT_W_CITY   307511 non-null  int64  
 32   WEEKDAY_APPR_PROCESS_START    307511 non-null  object 
 33   HOUR_APPR_PROCESS_START       307511 non-null  int64  
 34   REG_REGION_NOT_LIVE_REGION    307511 non-null  int64  
 35   REG_REGION_NOT_WORK_REGION    307511 non-null  int64  
 36   LIVE_REGION_NOT_WORK_REGION   307511 non-null  int64  
 37   REG_CITY_NOT_LIVE_CITY        307511 non-null  int64  
 38   REG_CITY_NOT_WORK_CITY        307511 non-null  int64  
 39   LIVE_CITY_NOT_WORK_CITY       307511 non-null  int64  
 40   ORGANIZATION_TYPE             307511 non-null  object 
 41   EXT_SOURCE_1                  134133 non-null  float64
 42   EXT_SOURCE_2                  306851 non-null  float64
 43   EXT_SOURCE_3                  246546 non-null  float64
 44   APARTMENTS_AVG                151450 non-null  float64
 45   BASEMENTAREA_AVG              127568 non-null  float64
 46   YEARS_BEGINEXPLUATATION_AVG   157504 non-null  float64
 47   YEARS_BUILD_AVG               103023 non-null  float64
 48   COMMONAREA_AVG                92646 non-null   float64
 49   ELEVATORS_AVG                 143620 non-null  float64
 50   ENTRANCES_AVG                 152683 non-null  float64
 51   FLOORSMAX_AVG                 154491 non-null  float64
 52   FLOORSMIN_AVG                 98869 non-null   float64
 53   LANDAREA_AVG                  124921 non-null  float64
 54   LIVINGAPARTMENTS_AVG          97312 non-null   float64
 55   LIVINGAREA_AVG                153161 non-null  float64
 56   NONLIVINGAPARTMENTS_AVG       93997 non-null   float64
 57   NONLIVINGAREA_AVG             137829 non-null  float64
 58   APARTMENTS_MODE               151450 non-null  float64
 59   BASEMENTAREA_MODE             127568 non-null  float64
 60   YEARS_BEGINEXPLUATATION_MODE  157504 non-null  float64
 61   YEARS_BUILD_MODE              103023 non-null  float64
 62   COMMONAREA_MODE               92646 non-null   float64
 63   ELEVATORS_MODE                143620 non-null  float64
 64   ENTRANCES_MODE                152683 non-null  float64
 65   FLOORSMAX_MODE                154491 non-null  float64
 66   FLOORSMIN_MODE                98869 non-null   float64
 67   LANDAREA_MODE                 124921 non-null  float64
 68   LIVINGAPARTMENTS_MODE         97312 non-null   float64
 69   LIVINGAREA_MODE               153161 non-null  float64
 70   NONLIVINGAPARTMENTS_MODE      93997 non-null   float64
 71   NONLIVINGAREA_MODE            137829 non-null  float64
 72   APARTMENTS_MEDI               151450 non-null  float64
 73   BASEMENTAREA_MEDI             127568 non-null  float64
 74   YEARS_BEGINEXPLUATATION_MEDI  157504 non-null  float64
 75   YEARS_BUILD_MEDI              103023 non-null  float64
 76   COMMONAREA_MEDI               92646 non-null   float64
 77   ELEVATORS_MEDI                143620 non-null  float64
 78   ENTRANCES_MEDI                152683 non-null  float64
 79   FLOORSMAX_MEDI                154491 non-null  float64
 80   FLOORSMIN_MEDI                98869 non-null   float64
 81   LANDAREA_MEDI                 124921 non-null  float64
 82   LIVINGAPARTMENTS_MEDI         97312 non-null   float64
 83   LIVINGAREA_MEDI               153161 non-null  float64
 84   NONLIVINGAPARTMENTS_MEDI      93997 non-null   float64
 85   NONLIVINGAREA_MEDI            137829 non-null  float64
 86   FONDKAPREMONT_MODE            97216 non-null   object 
 87   HOUSETYPE_MODE                153214 non-null  object 
 88   TOTALAREA_MODE                159080 non-null  float64
 89   WALLSMATERIAL_MODE            151170 non-null  object 
 90   EMERGENCYSTATE_MODE           161756 non-null  object 
 91   OBS_30_CNT_SOCIAL_CIRCLE      306490 non-null  float64
 92   DEF_30_CNT_SOCIAL_CIRCLE      306490 non-null  float64
 93   OBS_60_CNT_SOCIAL_CIRCLE      306490 non-null  float64
 94   DEF_60_CNT_SOCIAL_CIRCLE      306490 non-null  float64
 95   DAYS_LAST_PHONE_CHANGE        307510 non-null  float64
 96   FLAG_DOCUMENT_2               307511 non-null  int64  
 97   FLAG_DOCUMENT_3               307511 non-null  int64  
 98   FLAG_DOCUMENT_4               307511 non-null  int64  
 99   FLAG_DOCUMENT_5               307511 non-null  int64  
 100  FLAG_DOCUMENT_6               307511 non-null  int64  
 101  FLAG_DOCUMENT_7               307511 non-null  int64  
 102  FLAG_DOCUMENT_8               307511 non-null  int64  
 103  FLAG_DOCUMENT_9               307511 non-null  int64  
 104  FLAG_DOCUMENT_10              307511 non-null  int64  
 105  FLAG_DOCUMENT_11              307511 non-null  int64  
 106  FLAG_DOCUMENT_12              307511 non-null  int64  
 107  FLAG_DOCUMENT_13              307511 non-null  int64  
 108  FLAG_DOCUMENT_14              307511 non-null  int64  
 109  FLAG_DOCUMENT_15              307511 non-null  int64  
 110  FLAG_DOCUMENT_16              307511 non-null  int64  
 111  FLAG_DOCUMENT_17              307511 non-null  int64  
 112  FLAG_DOCUMENT_18              307511 non-null  int64  
 113  FLAG_DOCUMENT_19              307511 non-null  int64  
 114  FLAG_DOCUMENT_20              307511 non-null  int64  
 115  FLAG_DOCUMENT_21              307511 non-null  int64  
 116  AMT_REQ_CREDIT_BUREAU_HOUR    265992 non-null  float64
 117  AMT_REQ_CREDIT_BUREAU_DAY     265992 non-null  float64
 118  AMT_REQ_CREDIT_BUREAU_WEEK    265992 non-null  float64
 119  AMT_REQ_CREDIT_BUREAU_MON     265992 non-null  float64
 120  AMT_REQ_CREDIT_BUREAU_QRT     265992 non-null  float64
 121  AMT_REQ_CREDIT_BUREAU_YEAR    265992 non-null  float64
dtypes: float64(65), int64(41), object(16)
memory usage: 286.2+ MB


# Descriptive dataframe:

app_df.describe()


# checking the total number of missing values in each column:

app_df.isnull().sum().sort_values(ascending=False)

COMMONAREA_MEDI             214865
COMMONAREA_AVG              214865
COMMONAREA_MODE             214865
NONLIVINGAPARTMENTS_MODE    213514
NONLIVINGAPARTMENTS_AVG     213514
                             ...  
NAME_HOUSING_TYPE                0
NAME_FAMILY_STATUS               0
NAME_EDUCATION_TYPE              0
NAME_INCOME_TYPE                 0
SK_ID_CURR                       0
Length: 122, dtype: int64


# checking the percentage of missing values in each column:

(app_df.isnull().sum()/len(app_df)*100).sort_values(ascending=False)


# Sοme cοlumns in the dataset have missing values.
# Share οf missing values varies a lοt acrοss these variables
# We are gοing tο remοve cοlumns with high missing values percentage
# We are cοnsidering the threashοld value as 50%.

COMMONAREA_MEDI             69.872297
COMMONAREA_AVG              69.872297
COMMONAREA_MODE             69.872297
NONLIVINGAPARTMENTS_MODE    69.432963
NONLIVINGAPARTMENTS_AVG     69.432963
                              ...    
NAME_HOUSING_TYPE            0.000000
NAME_FAMILY_STATUS           0.000000
NAME_EDUCATION_TYPE          0.000000
NAME_INCOME_TYPE             0.000000
SK_ID_CURR                   0.000000
Length: 122, dtype: float64


# removing columns with high missing values percentage:

app_df= app_df.loc[:, (app_df.isnull().sum()/len(app_df)*100) < 50]   # here we consider 50%  as the threshold, removing the culumns which has more 50% of missing values.


# checking total no of rows and columns after removing high missing value columns:
# Dataframe shape after removing missing value columns:

app_df.shape

(307511, 81)


# checking the updated percentage of missing values in each column:

round(app_df.isnull().sum()/len(app_df)*100,2)

SK_ID_CURR                     0.0
TARGET                         0.0
NAME_CONTRACT_TYPE             0.0
CODE_GENDER                    0.0
FLAG_OWN_CAR                   0.0
                              ... 
AMT_REQ_CREDIT_BUREAU_DAY     13.5
AMT_REQ_CREDIT_BUREAU_WEEK    13.5
AMT_REQ_CREDIT_BUREAU_MON     13.5
AMT_REQ_CREDIT_BUREAU_QRT     13.5
AMT_REQ_CREDIT_BUREAU_YEAR    13.5
Length: 81, dtype: float64


# getting columns with null values greater than 0% and less than or equal to 40%:

temp=app_df.columns[((app_df.isnull().sum()/len(app_df)*100) <= 40) & ((app_df.isnull().sum()/len(app_df)*100) > 0)]
print(temp)
print(len(temp))

# We have 18 columns where null values is greater than 0% and less than or equal to 40%

Index(['AMT_ANNUITY', 'AMT_GOODS_PRICE', 'NAME_TYPE_SUITE', 'OCCUPATION_TYPE',
       'CNT_FAM_MEMBERS', 'EXT_SOURCE_2', 'EXT_SOURCE_3',
       'OBS_30_CNT_SOCIAL_CIRCLE', 'DEF_30_CNT_SOCIAL_CIRCLE',
       'OBS_60_CNT_SOCIAL_CIRCLE', 'DEF_60_CNT_SOCIAL_CIRCLE',
       'DAYS_LAST_PHONE_CHANGE', 'AMT_REQ_CREDIT_BUREAU_HOUR',
       'AMT_REQ_CREDIT_BUREAU_DAY', 'AMT_REQ_CREDIT_BUREAU_WEEK',
       'AMT_REQ_CREDIT_BUREAU_MON', 'AMT_REQ_CREDIT_BUREAU_QRT',
       'AMT_REQ_CREDIT_BUREAU_YEAR'],
      dtype='object')
18


# inspecting AMT_ANNUITY column:

app_df[app_df['AMT_ANNUITY'].isnull()]


# checking for outliers using statistical summary of AMT_ANNUITY column and boxplot:

print(app_df['AMT_ANNUITY'].describe(percentiles=[0.1,0.25,0.5,0.75,0.99]))
print()
plt.style.use('ggplot')
plt.figure(figsize=[10,5])
sns.boxplot(app_df['AMT_ANNUITY'])
plt.show()

count    307499.000000
mean      27108.573909
std       14493.737315
min        1615.500000
10%       11074.500000
25%       16524.000000
50%       24903.000000
75%       34596.000000
99%       70006.500000
max      258025.500000
Name: AMT_ANNUITY, dtype: float64


# inspecting AMT_GOODS_PRICE column:

app_df[app_df['AMT_GOODS_PRICE'].isnull()]


# checking for outliers using statistical summary of AMT_GOODS_PRICE column and boxplot:

print(round(app_df['AMT_GOODS_PRICE'].describe(percentiles=[0.1,0.25,0.5,0.75,0.99]),2))
print()
sns.boxplot(app_df['AMT_GOODS_PRICE'])
plt.show()

count     307233.00
mean      538396.21
std       369446.46
min        40500.00
10%       180000.00
25%       238500.00
50%       450000.00
75%       679500.00
99%      1800000.00
max      4050000.00
Name: AMT_GOODS_PRICE, dtype: float64


# checking for maximum repeated value in NAME_TYPE_SUITE:

print(app_df['NAME_TYPE_SUITE'].value_counts())
print(app_df['NAME_TYPE_SUITE'].mode()[0])

Unaccompanied      248526
Family              40149
Spouse, partner     11370
Children             3267
Other_B              1770
Other_A               866
Group of people       271
Name: NAME_TYPE_SUITE, dtype: int64
Unaccompanied


# inspecting AMT_GOODS_PRICE column:

app_df[app_df['CNT_FAM_MEMBERS'].isnull()]


# checking for outliers using statistical summary of CNT_FAM_MEMBERS column and boxplot:

print(round(app_df['CNT_FAM_MEMBERS'].describe(percentiles=[0.1,0.25,0.5,0.75,0.99]),2))
print()
sns.boxplot(app_df['CNT_FAM_MEMBERS'])
plt.show()

count    307509.00
mean          2.15
std           0.91
min           1.00
10%           1.00
25%           2.00
50%           2.00
75%           3.00
99%           5.00
max          20.00
Name: CNT_FAM_MEMBERS, dtype: float64


# checking for maximum repeated value in OCCUPATION_TYPE:

print(app_df['OCCUPATION_TYPE'].value_counts())
print(app_df['OCCUPATION_TYPE'].mode()[0])

Laborers                 55186
Sales staff              32102
Core staff               27570
Managers                 21371
Drivers                  18603
High skill tech staff    11380
Accountants               9813
Medicine staff            8537
Security staff            6721
Cooking staff             5946
Cleaning staff            4653
Private service staff     2652
Low-skill Laborers        2093
Waiters/barmen staff      1348
Secretaries               1305
Realty agents              751
HR staff                   563
IT staff                   526
Name: OCCUPATION_TYPE, dtype: int64
Laborers


# filling NaN values with others:

app_df['OCCUPATION_TYPE'].fillna(value = 'Unknown', inplace = True)


# plotting the count plot to see distribution of the column:

plt.figure(figsize=[10,5])
sns.countplot(data = app_df, y = "OCCUPATION_TYPE", color = "indianred")
plt.show()


print(round(app_df['EXT_SOURCE_2'].describe(percentiles=[0.1,0.25,0.5,0.75,0.99]),4))
print()
sns.boxplot(app_df['EXT_SOURCE_2'])
plt.show()

count    306851.0000
mean          0.5144
std           0.1911
min           0.0000
10%           0.2157
25%           0.3925
50%           0.5660
75%           0.6636
99%           0.7828
max           0.8550
Name: EXT_SOURCE_2, dtype: float64


print(round(app_df['AMT_CREDIT'].describe(percentiles=[0.1,0.25,0.5,0.75,0.99]),2))
print()
sns.boxplot(app_df['AMT_CREDIT'])
plt.show()

count     307511.00
mean      599026.00
std       402490.78
min        45000.00
10%       180000.00
25%       270000.00
50%       513531.00
75%       808650.00
99%      1854000.00
max      4050000.00
Name: AMT_CREDIT, dtype: float64


print(round(app_df['EXT_SOURCE_3'].describe(percentiles=[0.1,0.25,0.5,0.75,0.99]),4))
print()
sns.boxplot(app_df['EXT_SOURCE_3'])
plt.show()

count    246546.0000
mean          0.5109
std           0.1948
min           0.0005
10%           0.2276
25%           0.3706
50%           0.5353
75%           0.6691
99%           0.8328
max           0.8960
Name: EXT_SOURCE_3, dtype: float64


# checkimg the summary statistics for the columns:

app_df[['AMT_REQ_CREDIT_BUREAU_YEAR',
    'AMT_REQ_CREDIT_BUREAU_QRT',
    'AMT_REQ_CREDIT_BUREAU_MON',
    'AMT_REQ_CREDIT_BUREAU_WEEK',
    'AMT_REQ_CREDIT_BUREAU_DAY',
    'AMT_REQ_CREDIT_BUREAU_HOUR']].describe()


# checking the most recurring value for the columns
app_df[['AMT_REQ_CREDIT_BUREAU_YEAR',
    'AMT_REQ_CREDIT_BUREAU_QRT',
    'AMT_REQ_CREDIT_BUREAU_MON',
    'AMT_REQ_CREDIT_BUREAU_WEEK',
    'AMT_REQ_CREDIT_BUREAU_DAY',
    'AMT_REQ_CREDIT_BUREAU_HOUR']].mode()


print(round(app_df['OBS_30_CNT_SOCIAL_CIRCLE'].describe(percentiles=[0.1,0.25,0.5,0.75,0.99]),2))
print()
sns.boxplot(app_df['OBS_30_CNT_SOCIAL_CIRCLE'])
plt.show()

count    306490.00
mean          1.42
std           2.40
min           0.00
10%           0.00
25%           0.00
50%           0.00
75%           2.00
99%          10.00
max         348.00
Name: OBS_30_CNT_SOCIAL_CIRCLE, dtype: float64


# checking the distribution of genders
app_df['CODE_GENDER'].value_counts()

F      202448
M      105059
XNA         4
Name: CODE_GENDER, dtype: int64


# checking the rows where gender is XNA
app_df[app_df['CODE_GENDER'] == 'XNA']


# replacing XNA with F and then checking the count of males and females:

app_df['CODE_GENDER'] = app_df['CODE_GENDER'].apply(lambda x: 'F' if x == 'XNA' else x)
app_df['CODE_GENDER'].value_counts()

F    202452
M    105059
Name: CODE_GENDER, dtype: int64


# checking statistical summary of the column:

app_df['DAYS_BIRTH'].describe()

count    307511.000000
mean     -16036.995067
std        4363.988632
min      -25229.000000
25%      -19682.000000
50%      -15750.000000
75%      -12413.000000
max       -7489.000000
Name: DAYS_BIRTH, dtype: float64


# Converting DAYS_BIRTH to positive days:

app_df['DAYS_BIRTH'] = app_df['DAYS_BIRTH'].apply(lambda x: abs(x) if x < 0 else x)
app_df['DAYS_BIRTH'].describe()

count    307511.000000
mean      16036.995067
std        4363.988632
min        7489.000000
25%       12413.000000
50%       15750.000000
75%       19682.000000
max       25229.000000
Name: DAYS_BIRTH, dtype: float64


# creating a new column YEARS_BIRTH for ease of analysis:

app_df['YEARS_BIRTH'] = app_df['DAYS_BIRTH'].apply(lambda x: (x//365))
print(app_df['YEARS_BIRTH'].describe())
sns.boxplot(data=app_df, x='YEARS_BIRTH')
plt.show()

count    307511.000000
mean         43.435968
std          11.954593
min          20.000000
25%          34.000000
50%          43.000000
75%          53.000000
max          69.000000
Name: YEARS_BIRTH, dtype: float64


# checking the NAME_FAMILY_STATUS of applicants:

app_df['NAME_FAMILY_STATUS'].value_counts()

Married                 196432
Single / not married     45444
Civil marriage           29775
Separated                19770
Widow                    16088
Unknown                      2
Name: NAME_FAMILY_STATUS, dtype: int64


# checking the rows where NAME_FAMILY_STATUS is Unknown:

app_df[app_df['NAME_FAMILY_STATUS'] == 'Unknown']


# replacing 'Unknown' with 'Married':

app_df['NAME_FAMILY_STATUS'] = app_df['NAME_FAMILY_STATUS'].apply(lambda x: 'Married' if x == 'Unknown' else x)
app_df['NAME_FAMILY_STATUS'].value_counts()

Married                 196434
Single / not married     45444
Civil marriage           29775
Separated                19770
Widow                    16088
Name: NAME_FAMILY_STATUS, dtype: int64


# checking statistical summary of the column:

app_df['DAYS_EMPLOYED'].describe()

count    307511.000000
mean      63815.045904
std      141275.766519
min      -17912.000000
25%       -2760.000000
50%       -1213.000000
75%        -289.000000
max      365243.000000
Name: DAYS_EMPLOYED, dtype: float64


# Converting DAYS_EMPLOYED to positive days and checking statistical values:

app_df['DAYS_EMPLOYED'] = app_df['DAYS_EMPLOYED'].apply(lambda x: abs(x) if x < 0 else x)
print(app_df['DAYS_EMPLOYED'].describe())
print()
print(app_df['DAYS_EMPLOYED'].value_counts().head())
print()
print(app_df['DAYS_EMPLOYED'].value_counts(normalize=True).head())

count    307511.000000
mean      67724.742149
std      139443.751806
min           0.000000
25%         933.000000
50%        2219.000000
75%        5707.000000
max      365243.000000
Name: DAYS_EMPLOYED, dtype: float64

365243    55374
200         156
224         152
230         151
199         151
Name: DAYS_EMPLOYED, dtype: int64

365243    0.180072
200       0.000507
224       0.000494
230       0.000491
199       0.000491
Name: DAYS_EMPLOYED, dtype: float64


# checking type οf incοme fοr cοlumns where emplοyement days is 365243:

app_df[app_df['DAYS_EMPLOYED'] == 365243].NAME_INCOME_TYPE.value_counts()

Pensioner     55352
Unemployed       22
Name: NAME_INCOME_TYPE, dtype: int64


# creating a new column YEARS_EMPLOYED for ease of analysis:

app_df['YEARS_EMPLOYED'] = app_df['DAYS_EMPLOYED'].apply(lambda x: (x//365))
print(app_df['YEARS_EMPLOYED'].describe())
sns.boxplot(data=app_df, x='YEARS_EMPLOYED')
plt.show()

count    307511.000000
mean        185.021521
std         381.972190
min           0.000000
25%           2.000000
50%           6.000000
75%          15.000000
max        1000.000000
Name: YEARS_EMPLOYED, dtype: float64


# checking statistical summary of the column:

print(app_df['DAYS_REGISTRATION'].describe())
print()
print(app_df['DAYS_REGISTRATION'].value_counts())
print()
print(app_df['DAYS_REGISTRATION'].value_counts(normalize=True))

count    307511.000000
mean      -4986.120328
std        3522.886321
min      -24672.000000
25%       -7479.500000
50%       -4504.000000
75%       -2010.000000
max           0.000000
Name: DAYS_REGISTRATION, dtype: float64

-1.0        113
-7.0         98
-6.0         96
-4.0         92
-2.0         92
           ... 
-15581.0      1
-15031.0      1
-14804.0      1
-15008.0      1
-14798.0      1
Name: DAYS_REGISTRATION, Length: 15688, dtype: int64

-1.0        0.000367
-7.0        0.000319
-6.0        0.000312
-4.0        0.000299
-2.0        0.000299
              ...   
-15581.0    0.000003
-15031.0    0.000003
-14804.0    0.000003
-15008.0    0.000003
-14798.0    0.000003
Name: DAYS_REGISTRATION, Length: 15688, dtype: float64


# Converting DAYS_REGISTRATION to positive days and checking statistical values:

app_df['DAYS_REGISTRATION'] = app_df['DAYS_REGISTRATION'].apply(lambda x: abs(x) if x < 0 else x)
print(app_df['DAYS_REGISTRATION'].describe())

count    307511.000000
mean       4986.120328
std        3522.886321
min           0.000000
25%        2010.000000
50%        4504.000000
75%        7479.500000
max       24672.000000
Name: DAYS_REGISTRATION, dtype: float64


# creating a new column YEARS_REGISTRATION for ease of analysis:

app_df['YEARS_REGISTRATION'] = app_df['DAYS_REGISTRATION'].apply(lambda x: (x//365))
print(app_df['YEARS_REGISTRATION'].describe())
sns.boxplot(data=app_df, x='YEARS_REGISTRATION')
plt.show()

count    307511.000000
mean         13.168683
std           9.646841
min           0.000000
25%           5.000000
50%          12.000000
75%          20.000000
max          67.000000
Name: YEARS_REGISTRATION, dtype: float64


# checking statistical summary of the column:

print(app_df['DAYS_ID_PUBLISH'].describe())
print()
print(app_df['DAYS_ID_PUBLISH'].value_counts().head())
print()
print(app_df['DAYS_ID_PUBLISH'].value_counts(normalize=True).head())

count    307511.000000
mean      -2994.202373
std        1509.450419
min       -7197.000000
25%       -4299.000000
50%       -3254.000000
75%       -1720.000000
max           0.000000
Name: DAYS_ID_PUBLISH, dtype: float64

-4053    169
-4095    162
-4046    161
-4417    159
-4256    158
Name: DAYS_ID_PUBLISH, dtype: int64

-4053    0.000550
-4095    0.000527
-4046    0.000524
-4417    0.000517
-4256    0.000514
Name: DAYS_ID_PUBLISH, dtype: float64


# Converting DAYS_ID_PUBLISH to positive days and checking statistical values:

app_df['DAYS_ID_PUBLISH'] = app_df['DAYS_ID_PUBLISH'].apply(lambda x: abs(x) if x < 0 else x)
print(app_df['DAYS_ID_PUBLISH'].describe())

count    307511.000000
mean       2994.202373
std        1509.450419
min           0.000000
25%        1720.000000
50%        3254.000000
75%        4299.000000
max        7197.000000
Name: DAYS_ID_PUBLISH, dtype: float64


# creating a new column YEARS_ID_PUBLISH for ease of analysis:

app_df['YEARS_ID_PUBLISH'] = app_df['DAYS_ID_PUBLISH'].apply(lambda x: (x//365))
print(app_df['YEARS_ID_PUBLISH'].describe())
sns.boxplot(data=app_df, x='YEARS_ID_PUBLISH')
plt.show()

count    307511.000000
mean          7.713474
std           4.134515
min           0.000000
25%           4.000000
50%           8.000000
75%          11.000000
max          19.000000
Name: YEARS_ID_PUBLISH, dtype: float64


# checking statistical summary of the column:

print(app_df['DAYS_LAST_PHONE_CHANGE'].describe())
print()
print(app_df['DAYS_LAST_PHONE_CHANGE'].value_counts().head())
print()
print(app_df['DAYS_LAST_PHONE_CHANGE'].value_counts(normalize=True).head())

count    307510.000000
mean       -962.858788
std         826.808487
min       -4292.000000
25%       -1570.000000
50%        -757.000000
75%        -274.000000
max           0.000000
Name: DAYS_LAST_PHONE_CHANGE, dtype: float64

 0.0    37672
-1.0     2812
-2.0     2318
-3.0     1763
-4.0     1285
Name: DAYS_LAST_PHONE_CHANGE, dtype: int64

 0.0    0.122507
-1.0    0.009144
-2.0    0.007538
-3.0    0.005733
-4.0    0.004179
Name: DAYS_LAST_PHONE_CHANGE, dtype: float64


# Converting DAYS_LAST_PHONE_CHANGE to positive days and checking statistical values:

app_df['DAYS_LAST_PHONE_CHANGE'] = app_df['DAYS_LAST_PHONE_CHANGE'].apply(lambda x: abs(x) if x < 0 else x)
print(app_df['DAYS_LAST_PHONE_CHANGE'].describe())

count    307510.000000
mean        962.858788
std         826.808487
min           0.000000
25%         274.000000
50%         757.000000
75%        1570.000000
max        4292.000000
Name: DAYS_LAST_PHONE_CHANGE, dtype: float64


# creating a new column YEARS_LAST_PHONE_CHANGE for ease of analysis:

app_df['YEARS_LAST_PHONE_CHANGE'] = app_df['DAYS_LAST_PHONE_CHANGE'].apply(lambda x: (x//365))
print(app_df['YEARS_LAST_PHONE_CHANGE'].describe())
sns.boxplot(data=app_df, x='YEARS_LAST_PHONE_CHANGE')
plt.show()

count    307510.000000
mean          2.225115
std           2.193678
min           0.000000
25%           0.000000
50%           2.000000
75%           4.000000
max          11.000000
Name: YEARS_LAST_PHONE_CHANGE, dtype: float64


# checking the imbalance using countplot:

plt.style.use('ggplot')
plt.figure(figsize = [5,4])

sns.countplot(data=app_df, x='TARGET')
plt.title("Checking imbalance ratio of TARGET variable")
plt.xlabel("\n 0 - On-time Payment clients   |   1 - Clients with Payment Difficulty")
plt.show()


# checking exact Target 0 to Target 1 ratio:

app_df[app_df.TARGET==0].shape[0]/app_df[app_df.TARGET==1].shape[0]

11.387150050352467


# checking the imbalance using normalization:

plt.style.use('ggplot')
plt.figure(figsize = [5,4])
(app_df['TARGET'].value_counts(normalize=True)*100).plot.bar(color=['indianred', 'dodgerblue'])
plt.title("Checking imbalance ratio of TARGET variable")
plt.xticks(rotation = 0)
plt.xlabel("\n 0 - On-time Payment clients   |   1 - Clients with Payment Difficulty")
plt.show()


# inspecting TARGET column:

print(app_df['TARGET'].value_counts())
print()
print(app_df['TARGET'].value_counts(normalize=True))

0    282686
1     24825
Name: TARGET, dtype: int64

0    0.919271
1    0.080729
Name: TARGET, dtype: float64


# creating new dataframe with TARGET value:

df0 = app_df[app_df['TARGET'] == 0]
df1 = app_df[app_df['TARGET'] == 1]


# checking all columns with object type data and storing it in a list:

obj_plot=list(app_df.columns[app_df.dtypes=="object"])
obj_plot

['NAME_CONTRACT_TYPE',
 'CODE_GENDER',
 'FLAG_OWN_CAR',
 'FLAG_OWN_REALTY',
 'NAME_TYPE_SUITE',
 'NAME_INCOME_TYPE',
 'NAME_EDUCATION_TYPE',
 'NAME_FAMILY_STATUS',
 'NAME_HOUSING_TYPE',
 'OCCUPATION_TYPE',
 'WEEKDAY_APPR_PROCESS_START',
 'ORGANIZATION_TYPE',
 'EMERGENCYSTATE_MODE']


# checking each columns underlying groups:

for i in obj_plot:
    # for clients with payment difficulties
    print("--------------------------------------------------")
    print(f"{i} for clients with payment difficulties")
    print(df1[i].value_counts().sort_values(ascending = False))
    # for on-time payment clients
    print(f"\n{i} for on-time payment clients")
    print(df0[i].value_counts().sort_values(ascending = False))

--------------------------------------------------
NAME_CONTRACT_TYPE for clients with payment difficulties
Cash loans         23221
Revolving loans     1604
Name: NAME_CONTRACT_TYPE, dtype: int64

NAME_CONTRACT_TYPE for on-time payment clients
Cash loans         255011
Revolving loans     27675
Name: NAME_CONTRACT_TYPE, dtype: int64
--------------------------------------------------
CODE_GENDER for clients with payment difficulties
F    14170
M    10655
Name: CODE_GENDER, dtype: int64

CODE_GENDER for on-time payment clients
F    188282
M     94404
Name: CODE_GENDER, dtype: int64
--------------------------------------------------
FLAG_OWN_CAR for clients with payment difficulties
N    17249
Y     7576
Name: FLAG_OWN_CAR, dtype: int64

FLAG_OWN_CAR for on-time payment clients
N    185675
Y     97011
Name: FLAG_OWN_CAR, dtype: int64
--------------------------------------------------
FLAG_OWN_REALTY for clients with payment difficulties
Y    16983
N     7842
Name: FLAG_OWN_REALTY, dtype: int64

FLAG_OWN_REALTY for on-time payment clients
Y    196329
N     86357
Name: FLAG_OWN_REALTY, dtype: int64
--------------------------------------------------
NAME_TYPE_SUITE for clients with payment difficulties
Unaccompanied      20337
Family              3009
Spouse, partner      895
Children             241
Other_B              174
Other_A               76
Group of people       23
Name: NAME_TYPE_SUITE, dtype: int64

NAME_TYPE_SUITE for on-time payment clients
Unaccompanied      228189
Family              37140
Spouse, partner     10475
Children             3026
Other_B              1596
Other_A               790
Group of people       248
Name: NAME_TYPE_SUITE, dtype: int64
--------------------------------------------------
NAME_INCOME_TYPE for clients with payment difficulties
Working                 15224
Commercial associate     5360
Pensioner                2982
State servant            1249
Unemployed                  8
Maternity leave             2
Name: NAME_INCOME_TYPE, dtype: int64

NAME_INCOME_TYPE for on-time payment clients
Working                 143550
Commercial associate     66257
Pensioner                52380
State servant            20454
Student                     18
Unemployed                  14
Businessman                 10
Maternity leave              3
Name: NAME_INCOME_TYPE, dtype: int64
--------------------------------------------------
NAME_EDUCATION_TYPE for clients with payment difficulties
Secondary / secondary special    19524
Higher education                  4009
Incomplete higher                  872
Lower secondary                    417
Academic degree                      3
Name: NAME_EDUCATION_TYPE, dtype: int64

NAME_EDUCATION_TYPE for on-time payment clients
Secondary / secondary special    198867
Higher education                  70854
Incomplete higher                  9405
Lower secondary                    3399
Academic degree                     161
Name: NAME_EDUCATION_TYPE, dtype: int64
--------------------------------------------------
NAME_FAMILY_STATUS for clients with payment difficulties
Married                 14850
Single / not married     4457
Civil marriage           2961
Separated                1620
Widow                     937
Name: NAME_FAMILY_STATUS, dtype: int64

NAME_FAMILY_STATUS for on-time payment clients
Married                 181584
Single / not married     40987
Civil marriage           26814
Separated                18150
Widow                    15151
Name: NAME_FAMILY_STATUS, dtype: int64
--------------------------------------------------
NAME_HOUSING_TYPE for clients with payment difficulties
House / apartment      21272
With parents            1736
Municipal apartment      955
Rented apartment         601
Office apartment         172
Co-op apartment           89
Name: NAME_HOUSING_TYPE, dtype: int64

NAME_HOUSING_TYPE for on-time payment clients
House / apartment      251596
With parents            13104
Municipal apartment     10228
Rented apartment         4280
Office apartment         2445
Co-op apartment          1033
Name: NAME_HOUSING_TYPE, dtype: int64
--------------------------------------------------
OCCUPATION_TYPE for clients with payment difficulties
Unknown                  6278
Laborers                 5838
Sales staff              3092
Drivers                  2107
Core staff               1738
Managers                 1328
Security staff            722
High skill tech staff     701
Cooking staff             621
Medicine staff            572
Accountants               474
Cleaning staff            447
Low-skill Laborers        359
Private service staff     175
Waiters/barmen staff      152
Secretaries                92
Realty agents              59
HR staff                   36
IT staff                   34
Name: OCCUPATION_TYPE, dtype: int64

OCCUPATION_TYPE for on-time payment clients
Unknown                  90113
Laborers                 49348
Sales staff              29010
Core staff               25832
Managers                 20043
Drivers                  16496
High skill tech staff    10679
Accountants               9339
Medicine staff            7965
Security staff            5999
Cooking staff             5325
Cleaning staff            4206
Private service staff     2477
Low-skill Laborers        1734
Secretaries               1213
Waiters/barmen staff      1196
Realty agents              692
HR staff                   527
IT staff                   492
Name: OCCUPATION_TYPE, dtype: int64
--------------------------------------------------
WEEKDAY_APPR_PROCESS_START for clients with payment difficulties
TUESDAY      4501
WEDNESDAY    4238
FRIDAY       4101
THURSDAY     4098
MONDAY       3934
SATURDAY     2670
SUNDAY       1283
Name: WEEKDAY_APPR_PROCESS_START, dtype: int64

WEEKDAY_APPR_PROCESS_START for on-time payment clients
TUESDAY      49400
WEDNESDAY    47696
MONDAY       46780
THURSDAY     46493
FRIDAY       46237
SATURDAY     31182
SUNDAY       14898
Name: WEEKDAY_APPR_PROCESS_START, dtype: int64
--------------------------------------------------
ORGANIZATION_TYPE for clients with payment difficulties
Business Entity Type 3    6323
Self-employed             3908
XNA                       2990
Other                     1275
Business Entity Type 2     900
Construction               785
Trade: type 7              740
Medicine                   737
Government                 726
School                     526
Transport: type 4          501
Business Entity Type 1     487
Kindergarten               484
Trade: type 3              361
Industry: type 3           348
Security                   324
Agriculture                257
Housing                    235
Industry: type 11          234
Industry: type 9           225
Restaurant                 212
Transport: type 3          187
Postal                     182
Transport: type 2          172
Military                   135
Trade: type 2              133
Bank                       130
Police                     117
Industry: type 1           115
Industry: type 7           105
Services                   104
Security Ministries         96
Industry: type 4            89
University                  65
Electricity                 63
Hotel                       62
Telecom                     44
Realtor                     42
Industry: type 5            41
Emergency                   40
Advertising                 35
Insurance                   34
Industry: type 2            33
Trade: type 1               31
Cleaning                    29
Mobile                      29
Trade: type 6               29
Legal Services              24
Culture                     21
Industry: type 12           14
Industry: type 13            9
Transport: type 1            9
Industry: type 6             8
Industry: type 10            7
Religion                     5
Trade: type 5                3
Industry: type 8             3
Trade: type 4                2
Name: ORGANIZATION_TYPE, dtype: int64

ORGANIZATION_TYPE for on-time payment clients
Business Entity Type 3    61669
XNA                       52384
Self-employed             34504
Other                     15408
Medicine                  10456
Government                 9678
Business Entity Type 2     9653
School                     8367
Trade: type 7              7091
Kindergarten               6396
Construction               5936
Business Entity Type 1     5497
Transport: type 4          4897
Industry: type 9           3143
Trade: type 3              3131
Industry: type 3           2930
Security                   2923
Housing                    2723
Military                   2499
Industry: type 11          2470
Bank                       2377
Police                     2224
Agriculture                2197
Transport: type 2          2032
Postal                     1975
Security Ministries        1878
Trade: type 2              1767
Restaurant                 1599
Services                   1471
University                 1262
Industry: type 7           1202
Transport: type 3          1000
Industry: type 1            924
Hotel                       904
Electricity                 887
Industry: type 4            788
Trade: type 6               602
Insurance                   563
Industry: type 5            558
Telecom                     533
Emergency                   520
Industry: type 2            425
Advertising                 394
Culture                     358
Industry: type 12           355
Realtor                     354
Trade: type 1               317
Mobile                      288
Legal Services              281
Cleaning                    231
Transport: type 1           192
Industry: type 6            104
Industry: type 10           102
Religion                     80
Trade: type 4                62
Industry: type 13            58
Trade: type 5                46
Industry: type 8             21
Name: ORGANIZATION_TYPE, dtype: int64
--------------------------------------------------
EMERGENCYSTATE_MODE for clients with payment difficulties
No     11104
Yes      223
Name: EMERGENCYSTATE_MODE, dtype: int64

EMERGENCYSTATE_MODE for on-time payment clients
No     148324
Yes      2105
Name: EMERGENCYSTATE_MODE, dtype: int64


# Plotting a count plot on TARGET's object columns:

c_plot=['NAME_CONTRACT_TYPE','CODE_GENDER','FLAG_OWN_CAR','FLAG_OWN_REALTY','NAME_INCOME_TYPE','NAME_EDUCATION_TYPE','NAME_FAMILY_STATUS','NAME_HOUSING_TYPE','OCCUPATION_TYPE','WEEKDAY_APPR_PROCESS_START','ORGANIZATION_TYPE']
for i in c_plot:
    plt.style.use('ggplot')
    plt.figure(figsize = [18,8])
    # for clients with payment difficulties
    plt.subplot(1,2,1)
    plt.title(f'{i} of clients with payment difficulties')
    sns.countplot(data=df1, x =i, order = sorted(df1[i].unique(), reverse = True))
    plt.xticks(rotation = 90)
    # for on-time payment clients
    plt.subplot(1,2,2)
    plt.title(f'{i} of clients with on-time payments')
    sns.countplot(data=df0, x =i, order = sorted(df1[i].unique(), reverse = True))
    plt.xticks(rotation = 90)
    plt.tight_layout(pad = 4)
    plt.show()


# Plotting a pie chart on TARGET's object columns:

b_plot=['NAME_INCOME_TYPE','NAME_EDUCATION_TYPE','NAME_HOUSING_TYPE','OCCUPATION_TYPE','WEEKDAY_APPR_PROCESS_START','ORGANIZATION_TYPE']
for i in b_plot:
    plt.figure(figsize = [20,8])
    # for clients with payment difficulties
    plt.subplot(1,2,1)
    (df1[i].value_counts(normalize=True)*100).plot.bar(title = i + " of clients with payment difficulties in %", color=['indianred', 'dodgerblue', 'darkcyan', 'lightslategrey', 'lightseagreen'])
    plt.xticks(rotation=90)
    # for on-time payment clients
    plt.subplot(1,2,2)
    (df0[i].value_counts(normalize=True)*100).plot.bar(title = i + " of clients with on-Time Payments in %", color=['indianred', 'dodgerblue', 'darkcyan', 'lightslategrey', 'lightseagreen' ])
    plt.xticks(rotation=90)
    plt.tight_layout(pad = 4)
    plt.show()


p_plot=['NAME_CONTRACT_TYPE','CODE_GENDER','FLAG_OWN_CAR','FLAG_OWN_REALTY','NAME_FAMILY_STATUS','WEEKDAY_APPR_PROCESS_START']
# Plotting a pie chart on TARGET's object columns
for i in p_plot:
    plt.style.use('ggplot')
    plt.figure(figsize = [20,12])
    # for clients with payment difficulties
    plt.subplot(1,2,1)
    plt.title(f'{i} of clients with payment difficulties')
    df1[i].value_counts().plot.pie(autopct='%1.1f%%',shadow=True, startangle=60, labeldistance=None)
    plt.legend()
    # for on-time payment clients
    plt.subplot(1,2,2)
    plt.title(f'{i} of clients with on-time payments')
    df0[i].value_counts().plot.pie(autopct='%1.1f%%',shadow=True, startangle=60, labeldistance=None)
    plt.legend()
    plt.tight_layout(pad = 4)
    plt.show()


# checking out total number of numeric columns:

app_df.columns[(app_df.dtypes=="int64") | (app_df.dtypes=="float64")]

Index(['SK_ID_CURR', 'TARGET', 'CNT_CHILDREN', 'AMT_INCOME_TOTAL',
       'AMT_CREDIT', 'AMT_ANNUITY', 'AMT_GOODS_PRICE',
       'REGION_POPULATION_RELATIVE', 'DAYS_BIRTH', 'DAYS_EMPLOYED',
       'DAYS_REGISTRATION', 'DAYS_ID_PUBLISH', 'FLAG_MOBIL', 'FLAG_EMP_PHONE',
       'FLAG_WORK_PHONE', 'FLAG_CONT_MOBILE', 'FLAG_PHONE', 'FLAG_EMAIL',
       'CNT_FAM_MEMBERS', 'REGION_RATING_CLIENT',
       'REGION_RATING_CLIENT_W_CITY', 'HOUR_APPR_PROCESS_START',
       'REG_REGION_NOT_LIVE_REGION', 'REG_REGION_NOT_WORK_REGION',
       'LIVE_REGION_NOT_WORK_REGION', 'REG_CITY_NOT_LIVE_CITY',
       'REG_CITY_NOT_WORK_CITY', 'LIVE_CITY_NOT_WORK_CITY', 'EXT_SOURCE_2',
       'EXT_SOURCE_3', 'YEARS_BEGINEXPLUATATION_AVG', 'FLOORSMAX_AVG',
       'YEARS_BEGINEXPLUATATION_MODE', 'FLOORSMAX_MODE',
       'YEARS_BEGINEXPLUATATION_MEDI', 'FLOORSMAX_MEDI', 'TOTALAREA_MODE',
       'OBS_30_CNT_SOCIAL_CIRCLE', 'DEF_30_CNT_SOCIAL_CIRCLE',
       'OBS_60_CNT_SOCIAL_CIRCLE', 'DEF_60_CNT_SOCIAL_CIRCLE',
       'DAYS_LAST_PHONE_CHANGE', 'FLAG_DOCUMENT_2', 'FLAG_DOCUMENT_3',
       'FLAG_DOCUMENT_4', 'FLAG_DOCUMENT_5', 'FLAG_DOCUMENT_6',
       'FLAG_DOCUMENT_7', 'FLAG_DOCUMENT_8', 'FLAG_DOCUMENT_9',
       'FLAG_DOCUMENT_10', 'FLAG_DOCUMENT_11', 'FLAG_DOCUMENT_12',
       'FLAG_DOCUMENT_13', 'FLAG_DOCUMENT_14', 'FLAG_DOCUMENT_15',
       'FLAG_DOCUMENT_16', 'FLAG_DOCUMENT_17', 'FLAG_DOCUMENT_18',
       'FLAG_DOCUMENT_19', 'FLAG_DOCUMENT_20', 'FLAG_DOCUMENT_21',
       'AMT_REQ_CREDIT_BUREAU_HOUR', 'AMT_REQ_CREDIT_BUREAU_DAY',
       'AMT_REQ_CREDIT_BUREAU_WEEK', 'AMT_REQ_CREDIT_BUREAU_MON',
       'AMT_REQ_CREDIT_BUREAU_QRT', 'AMT_REQ_CREDIT_BUREAU_YEAR',
       'YEARS_BIRTH', 'YEARS_EMPLOYED', 'YEARS_REGISTRATION',
       'YEARS_ID_PUBLISH', 'YEARS_LAST_PHONE_CHANGE'],
      dtype='object')


# deleting all the Flag columns :

for i in app_df.columns:
    if i.startswith("FLAG"):
        app_df.drop(columns=i, inplace=True)


# plotting a dist plot without outliers:

d_plot=['AMT_CREDIT','YEARS_BIRTH','AMT_GOODS_PRICE','DAYS_EMPLOYED','CNT_CHILDREN','AMT_INCOME_TOTAL']
for i in d_plot:
    # calculationg IQR for clients with payment difficulties
    df1_Q1 = df1[i].quantile(0.25)
    df1_Q3 = df1[i].quantile(0.75)
    df1_IQR = df1_Q3 - df1_Q1
    Min_value1 = (df1_Q1 - 1.5 * df1_IQR)
    Max_value1 = (df1_Q3 + 1.5 * df1_IQR)
    # calculationg IQR for clients with on-time payments
    df0_Q1 = df0[i].quantile(0.25)
    df0_Q3 = df0[i].quantile(0.75)
    df0_IQR = df0_Q3 - df1_Q1
    Min_value0 = (df0_Q1 - 1.5 * df0_IQR)
    Max_value0 = (df0_Q3 + 1.5 * df0_IQR)
    #Removing outliers and plotting distplot
    plt.figure(figsize = [20,8])
    sns.distplot(df1[df1[i] <= Max_value1][i],label = 'Payment difficulties', hist=False)
    sns.distplot(df0[df0[i] <= Max_value0][i],label = 'On-Time Payments', hist=False)
    plt.title(f'{i} of clients')
    plt.ticklabel_format(style='plain', axis='x')
    plt.legend()
    plt.show()


# binning YEARS_BIRTH and creating new column "AGE_GROUP":

app_df['AGE_GROUP']= pd.cut(app_df['YEARS_BIRTH'],bins=[15,25,35,45,55,65,75])
(app_df['AGE_GROUP'].value_counts(normalize=True)*100).plot.barh(title ="Age group of clients applying for loan", color=['indianred', 'dodgerblue', 'darkcyan', 'lightslategrey', 'lightseagreen' ])
plt.xticks(rotation=0)
plt.show()


# binning 'AMT_INCOME_TOTAL' and Creating new column "INCOME_GROUP":

app_df['INCOME_GROUP']= pd.qcut(app_df['AMT_INCOME_TOTAL'], q=[0,0.1,0.3,0.6,0.8,1],labels=['VeryLow','Low','Medium','High','VeryHigh'])
(app_df['INCOME_GROUP'].value_counts(normalize=True)*100).plot.barh(title ="Salary group of clients applying for loan", color=['indianred', 'dodgerblue', 'darkcyan', 'lightslategrey', 'lightseagreen' ])
plt.xticks(rotation=0)
plt.show()


l1=['AMT_GOODS_PRICE','AMT_ANNUITY','DAYS_EMPLOYED','DAYS_BIRTH']
for a in range(len(l1)):
     print(l1[a])

AMT_GOODS_PRICE
AMT_ANNUITY
DAYS_EMPLOYED
DAYS_BIRTH


# function to calculate min max value for IQR:

def outlier_range(dataset,column):
    Q1 = dataset[column].quantile(0.25)
    Q3 = dataset[column].quantile(0.75)
    IQR = Q3 - Q1
    Min_value = (Q1 - 1.5 * IQR)
    Max_value = (Q3 + 1.5 * IQR)
    return Max_value


# outlier analysis of AMT_GOODS_PRICE V/S AMT_CREDIT:

max_value1_AMT_GOODS_PRICE = outlier_range(df1,'AMT_GOODS_PRICE')
max_value1_AMT_CREDIT = outlier_range(df1,'AMT_CREDIT')
max_value0_AMT_GOODS_PRICE = outlier_range(df0,'AMT_GOODS_PRICE')
max_value0_AMT_CREDIT = outlier_range(df0,'AMT_CREDIT')


# plotting a scatter plot to see the relation:

plt.figure(figsize = [20,8])
plt.subplot(1,2,1)
plt.title('Payment difficulties')
sns.scatterplot(x = df1[df1['AMT_GOODS_PRICE'] < max_value1_AMT_GOODS_PRICE].AMT_GOODS_PRICE, y = df1[df1['AMT_CREDIT'] < max_value1_AMT_CREDIT].AMT_CREDIT, data = df1)
plt.ticklabel_format(style='plain', axis='x')
plt.ticklabel_format(style='plain', axis='y')
plt.subplot(1,2,2)
plt.title('On-Time Payments')
sns.scatterplot(x = df0[df0['AMT_GOODS_PRICE'] < max_value0_AMT_GOODS_PRICE].AMT_GOODS_PRICE, y = df0[df0['AMT_CREDIT'] < max_value0_AMT_CREDIT].AMT_CREDIT, data = df0)
plt.ticklabel_format(style='plain', axis='x')
plt.ticklabel_format(style='plain', axis='y')
plt.tight_layout(pad = 4)
plt.show()


# outlier analysis of AMT_ANNUITY V/S AMT_CREDIT:

max_value1_AMT_ANNUITY = outlier_range(df1,'AMT_ANNUITY')
max_value1_AMT_CREDIT = outlier_range(df1,'AMT_CREDIT')
max_value0_AMT_ANNUITY = outlier_range(df0,'AMT_ANNUITY')
max_value0_AMT_CREDIT = outlier_range(df0,'AMT_CREDIT')


# plotting a scatter plot to see the relation:

plt.figure(figsize = [20,8])
plt.subplot(1,2,1)
plt.title('Payment difficulties')
sns.scatterplot(x = df1[df1['AMT_ANNUITY'] < max_value1_AMT_ANNUITY].AMT_ANNUITY, y = df1[df1['AMT_CREDIT'] < max_value1_AMT_CREDIT].AMT_CREDIT, data = df1)
plt.ticklabel_format(style='plain', axis='x')
plt.ticklabel_format(style='plain', axis='y')
plt.subplot(1,2,2)
plt.title('On-Time Payments')
sns.scatterplot(x = df0[df0['AMT_ANNUITY'] < max_value0_AMT_ANNUITY].AMT_ANNUITY, y = df0[df0['AMT_CREDIT'] < max_value0_AMT_CREDIT].AMT_CREDIT, data = df0)
plt.ticklabel_format(style='plain', axis='x')
plt.ticklabel_format(style='plain', axis='y')
plt.tight_layout(pad = 4)
plt.show()


# outlier analysis of DAYS_EMPLOYED V/S AMT_INCOME_TOTAL:

max_value1_DAYS_EMPLOYED = outlier_range(df1,'DAYS_EMPLOYED')
max_value1_AMT_INCOME_TOTAL = outlier_range(df1,'AMT_INCOME_TOTAL')
max_value0_DAYS_EMPLOYED = outlier_range(df0,'DAYS_EMPLOYED')
max_value0_AMT_INCOME_TOTAL = outlier_range(df0,'AMT_INCOME_TOTAL')


# plotting a scatter plot to see the relation:

plt.figure(figsize = [20,8])
plt.subplot(1,2,1)
plt.title('Payment difficulties')
sns.scatterplot(x = df1[df1['DAYS_EMPLOYED'] < max_value1_DAYS_EMPLOYED].DAYS_EMPLOYED, y = df1[df1['AMT_INCOME_TOTAL'] < max_value1_AMT_INCOME_TOTAL].AMT_INCOME_TOTAL, data = df1)
plt.ticklabel_format(style='plain', axis='x')
plt.ticklabel_format(style='plain', axis='y')
plt.subplot(1,2,2)
plt.title('On-Time Payments')
sns.scatterplot(x = df0[df0['DAYS_EMPLOYED'] < max_value0_DAYS_EMPLOYED].DAYS_EMPLOYED, y = df0[df0['AMT_INCOME_TOTAL'] < max_value0_AMT_INCOME_TOTAL].AMT_INCOME_TOTAL, data = df0)
plt.ticklabel_format(style='plain', axis='x')
plt.ticklabel_format(style='plain', axis='y')
plt.tight_layout(pad = 4)
plt.show()


# outlier analysis of AMT_ANNUITY V/S AMT_GOODS_PRICE:

max_value1_AMT_CREDIT = outlier_range(df1,'AMT_ANNUITY')
max_value1_DAYS_BIRTH = outlier_range(df1,'AMT_GOODS_PRICE')
max_value0_AMT_CREDIT = outlier_range(df0,'AMT_ANNUITY')
max_value0_DAYS_BIRTH = outlier_range(df0,'AMT_GOODS_PRICE')


# plotting a scatter plot to see the relation:

plt.figure(figsize = [20,8])
plt.subplot(1,2,1)
plt.title('Payment difficulties')
sns.scatterplot(x = df1[df1['AMT_ANNUITY'] < max_value1_AMT_ANNUITY].AMT_ANNUITY, y = df1[df1['AMT_GOODS_PRICE'] < max_value1_AMT_GOODS_PRICE].AMT_GOODS_PRICE, data = df1)
plt.ticklabel_format(style='plain', axis='x')
plt.ticklabel_format(style='plain', axis='y')
plt.subplot(1,2,2)
plt.title('On-Time Payments')
sns.scatterplot(x = df0[df0['AMT_ANNUITY'] < max_value0_AMT_ANNUITY].AMT_ANNUITY, y = df0[df0['AMT_GOODS_PRICE'] < max_value0_AMT_GOODS_PRICE].AMT_GOODS_PRICE, data = df0)
plt.ticklabel_format(style='plain', axis='x')
plt.ticklabel_format(style='plain', axis='y')
plt.tight_layout(pad = 4)
plt.show()


# outlier analysis of NAME_EDUCATION_TYPE V/S AMT_CREDIT V/S CODE_GENDER:

max_value1_AMT_CREDIT = outlier_range(df1,'AMT_CREDIT')
max_value0_AMT_CREDIT = outlier_range(df0,'AMT_CREDIT')


df1.groupby(by = ['NAME_EDUCATION_TYPE','CODE_GENDER']).AMT_CREDIT.describe().head()


# function to perform categorical analysis:

df0=app_df[app_df['TARGET']==0]
df1=app_df[app_df['TARGET']==1]
plt.figure(figsize = (18,5))
plt.subplot(1,2,1)
plt.title("Income Group and Amount Credited for On-time payers")
sns.boxplot(x = 'INCOME_GROUP', y = 'AMT_CREDIT', data =df0, showfliers=False, palette="Blues_d")
plt.subplot(1,2,2)
plt.title("Income Grp and Amount Credited for Defaulters")
sns.boxplot(x = 'INCOME_GROUP', y = 'AMT_CREDIT', data = df1, showfliers=False, palette="Blues_d")
plt.show()


#Checking the same columns to see the affect of Very High Income group:

plt.figure(figsize = (20,4))
plt.subplot(1,2,1)
plt.title("Income Group VS Payment Diffculty")
sns.countplot('INCOME_GROUP', hue = 'TARGET', data =app_df)
plt.show()


# plοt for Education Type and Amt Credited:

plt.figure(figsize = (20,5))
plt.subplot(1,2,1)
plt.title("Education Type and Amt Credited for On-Time payers")
sns.boxplot(x = 'NAME_EDUCATION_TYPE', y = 'AMT_CREDIT', data =df0,showfliers=False, palette="Blues_d")
plt.xticks(rotation=20, fontsize= 10,fontweight= 2)
plt.subplot(1,2,2)
plt.title("Education Type and Amt Credited for Defaulters")
sns.boxplot(x = 'NAME_EDUCATION_TYPE', y = 'AMT_CREDIT', data = df1,showfliers=False, palette="Blues_d")
plt.xticks(rotation=20, fontsize= 10,fontweight= 2)
plt.show()


# plot to check male Vs female default rate:

plt.figure(figsize = (20,4))
plt.subplot(1,2,1)
plt.title("NAME_CONTRACT_TYPE and TARGET for M")
sns.countplot('NAME_CONTRACT_TYPE', hue = "TARGET", data=app_df[(app_df['CODE_GENDER']=="M")])
plt.subplot(1,2,2)
plt.title("NAME_CONTRACT_TYPE and TARGET for F")
sns.countplot('NAME_CONTRACT_TYPE', hue = "TARGET", data=app_df[(app_df['CODE_GENDER']=="F")])
plt.show()


# creating a HeatMap to view the correlations above 80% and 99.99%:

for i in app_df.columns:
    if i.startswith("FLAG"):
        app_df.drop(columns=i, inplace=True)
corr_df1 = df1.select_dtypes(include=["int64","float64"]).corr()

plt.figure(figsize = (25,25))
sns.heatmap(data = corr_df1, annot = True, cmap = "Blues", cbar = True, fmt='.2f')
plt.show()


# getting top 10 correlations for Payment Difficulties:

corr_df1[corr_df1 <= 0.99].unstack().sort_values(ascending = False).head(20)

FLOORSMAX_MODE                FLOORSMAX_MEDI                  0.989195
FLOORSMAX_MEDI                FLOORSMAX_MODE                  0.989195
YEARS_LAST_PHONE_CHANGE       DAYS_LAST_PHONE_CHANGE          0.988086
DAYS_LAST_PHONE_CHANGE        YEARS_LAST_PHONE_CHANGE         0.988086
FLOORSMAX_AVG                 FLOORSMAX_MODE                  0.986594
FLOORSMAX_MODE                FLOORSMAX_AVG                   0.986594
AMT_GOODS_PRICE               AMT_CREDIT                      0.983103
AMT_CREDIT                    AMT_GOODS_PRICE                 0.983103
YEARS_BEGINEXPLUATATION_MODE  YEARS_BEGINEXPLUATATION_AVG     0.980466
YEARS_BEGINEXPLUATATION_AVG   YEARS_BEGINEXPLUATATION_MODE    0.980466
YEARS_BEGINEXPLUATATION_MODE  YEARS_BEGINEXPLUATATION_MEDI    0.978073
YEARS_BEGINEXPLUATATION_MEDI  YEARS_BEGINEXPLUATATION_MODE    0.978073
REGION_RATING_CLIENT_W_CITY   REGION_RATING_CLIENT            0.956637
REGION_RATING_CLIENT          REGION_RATING_CLIENT_W_CITY     0.956637
CNT_FAM_MEMBERS               CNT_CHILDREN                    0.885484
CNT_CHILDREN                  CNT_FAM_MEMBERS                 0.885484
DEF_30_CNT_SOCIAL_CIRCLE      DEF_60_CNT_SOCIAL_CIRCLE        0.868994
DEF_60_CNT_SOCIAL_CIRCLE      DEF_30_CNT_SOCIAL_CIRCLE        0.868994
REG_REGION_NOT_WORK_REGION    LIVE_REGION_NOT_WORK_REGION     0.847885
LIVE_REGION_NOT_WORK_REGION   REG_REGION_NOT_WORK_REGION      0.847885
dtype: float64


#Analysing relationship of AMT_CREDIT with AGE GROUP and income group:

plt.figure(figsize=[12,10])
f,(ax1,ax2,ax3) = plt.subplots(1,3,sharey=True,gridspec_kw={'width_ratios':[1,1, 0.001]},figsize=(14,6))
res=pd.pivot_table(data=df0, index="INCOME_GROUP",columns="AGE_GROUP",values='AMT_CREDIT', aggfunc="mean")
c=sns.heatmap(res, annot=True,cmap='Greens', fmt="g", ax=ax1, cbar=False)
c.set_xlabel('Target 0 \n', fontsize=14,fontweight= 7)
c.set_xticklabels(c.get_xticklabels(), rotation = 0, fontsize = 10)
res1=pd.pivot_table(data=df1, index="INCOME_GROUP",columns="AGE_GROUP",values='AMT_CREDIT', aggfunc="mean")
c1=sns.heatmap(res1, annot=True,cmap='Greens', fmt="g", ax=ax2, cbar=False)
c1.set_xlabel('Target 1 \n', fontsize=14,fontweight= 7,)
c1.set_xticklabels(c1.get_xticklabels(), rotation = 0, fontsize = 10)
c.set_yticklabels(c.get_yticklabels(), rotation = 0, fontsize = 10)
plt.tight_layout()
plt.show()

<Figure size 1200x1000 with 0 Axes>


# checking the top 5 rows:

prev_df.head() #The header row looks fine


# checking the bottom 5 rows:

prev_df.tail()

# The bottom rows looks fine.
# There are no junk values like page number, NaN values in bottom most row


# checking total no of rows and columns:

prev_df.shape #Dataframe has 1670214 rows and 37 columns

(1670214, 37)


# checking the info of the dataframe:

prev_df.info(null_counts=True, verbose=True)

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1670214 entries, 0 to 1670213
Data columns (total 37 columns):
 #   Column                       Non-Null Count    Dtype  
---  ------                       --------------    -----  
 0   SK_ID_PREV                   1670214 non-null  int64  
 1   SK_ID_CURR                   1670214 non-null  int64  
 2   NAME_CONTRACT_TYPE           1670214 non-null  object 
 3   AMT_ANNUITY                  1297979 non-null  float64
 4   AMT_APPLICATION              1670214 non-null  float64
 5   AMT_CREDIT                   1670213 non-null  float64
 6   AMT_DOWN_PAYMENT             774370 non-null   float64
 7   AMT_GOODS_PRICE              1284699 non-null  float64
 8   WEEKDAY_APPR_PROCESS_START   1670214 non-null  object 
 9   HOUR_APPR_PROCESS_START      1670214 non-null  int64  
 10  FLAG_LAST_APPL_PER_CONTRACT  1670214 non-null  object 
 11  NFLAG_LAST_APPL_IN_DAY       1670214 non-null  int64  
 12  RATE_DOWN_PAYMENT            774370 non-null   float64
 13  RATE_INTEREST_PRIMARY        5951 non-null     float64
 14  RATE_INTEREST_PRIVILEGED     5951 non-null     float64
 15  NAME_CASH_LOAN_PURPOSE       1670214 non-null  object 
 16  NAME_CONTRACT_STATUS         1670214 non-null  object 
 17  DAYS_DECISION                1670214 non-null  int64  
 18  NAME_PAYMENT_TYPE            1670214 non-null  object 
 19  CODE_REJECT_REASON           1670214 non-null  object 
 20  NAME_TYPE_SUITE              849809 non-null   object 
 21  NAME_CLIENT_TYPE             1670214 non-null  object 
 22  NAME_GOODS_CATEGORY          1670214 non-null  object 
 23  NAME_PORTFOLIO               1670214 non-null  object 
 24  NAME_PRODUCT_TYPE            1670214 non-null  object 
 25  CHANNEL_TYPE                 1670214 non-null  object 
 26  SELLERPLACE_AREA             1670214 non-null  int64  
 27  NAME_SELLER_INDUSTRY         1670214 non-null  object 
 28  CNT_PAYMENT                  1297984 non-null  float64
 29  NAME_YIELD_GROUP             1670214 non-null  object 
 30  PRODUCT_COMBINATION          1669868 non-null  object 
 31  DAYS_FIRST_DRAWING           997149 non-null   float64
 32  DAYS_FIRST_DUE               997149 non-null   float64
 33  DAYS_LAST_DUE_1ST_VERSION    997149 non-null   float64
 34  DAYS_LAST_DUE                997149 non-null   float64
 35  DAYS_TERMINATION             997149 non-null   float64
 36  NFLAG_INSURED_ON_APPROVAL    997149 non-null   float64
dtypes: float64(15), int64(6), object(16)
memory usage: 471.5+ MB


# checking the statistics summary of the dataframe:

prev_df.describe()


# Finding percentage of missing values:

prev_df.isna().sum()/len(prev_df)*100

SK_ID_PREV                      0.000000
SK_ID_CURR                      0.000000
NAME_CONTRACT_TYPE              0.000000
AMT_ANNUITY                    22.286665
AMT_APPLICATION                 0.000000
AMT_CREDIT                      0.000060
AMT_DOWN_PAYMENT               53.636480
AMT_GOODS_PRICE                23.081773
WEEKDAY_APPR_PROCESS_START      0.000000
HOUR_APPR_PROCESS_START         0.000000
FLAG_LAST_APPL_PER_CONTRACT     0.000000
NFLAG_LAST_APPL_IN_DAY          0.000000
RATE_DOWN_PAYMENT              53.636480
RATE_INTEREST_PRIMARY          99.643698
RATE_INTEREST_PRIVILEGED       99.643698
NAME_CASH_LOAN_PURPOSE          0.000000
NAME_CONTRACT_STATUS            0.000000
DAYS_DECISION                   0.000000
NAME_PAYMENT_TYPE               0.000000
CODE_REJECT_REASON              0.000000
NAME_TYPE_SUITE                49.119754
NAME_CLIENT_TYPE                0.000000
NAME_GOODS_CATEGORY             0.000000
NAME_PORTFOLIO                  0.000000
NAME_PRODUCT_TYPE               0.000000
CHANNEL_TYPE                    0.000000
SELLERPLACE_AREA                0.000000
NAME_SELLER_INDUSTRY            0.000000
CNT_PAYMENT                    22.286366
NAME_YIELD_GROUP                0.000000
PRODUCT_COMBINATION             0.020716
DAYS_FIRST_DRAWING             40.298129
DAYS_FIRST_DUE                 40.298129
DAYS_LAST_DUE_1ST_VERSION      40.298129
DAYS_LAST_DUE                  40.298129
DAYS_TERMINATION               40.298129
NFLAG_INSURED_ON_APPROVAL      40.298129
dtype: float64


nullvalue = prev_df.isnull().sum()/len(prev_df)*100
nullvalue = nullvalue[nullvalue.values>35]
nullvalue = list(nullvalue.index)
prev_df.drop(labels=nullvalue, axis=1, inplace=True)
prev_df.isnull().sum()/len(prev_df)*100

SK_ID_PREV                      0.000000
SK_ID_CURR                      0.000000
NAME_CONTRACT_TYPE              0.000000
AMT_ANNUITY                    22.286665
AMT_APPLICATION                 0.000000
AMT_CREDIT                      0.000060
AMT_GOODS_PRICE                23.081773
WEEKDAY_APPR_PROCESS_START      0.000000
HOUR_APPR_PROCESS_START         0.000000
FLAG_LAST_APPL_PER_CONTRACT     0.000000
NFLAG_LAST_APPL_IN_DAY          0.000000
NAME_CASH_LOAN_PURPOSE          0.000000
NAME_CONTRACT_STATUS            0.000000
DAYS_DECISION                   0.000000
NAME_PAYMENT_TYPE               0.000000
CODE_REJECT_REASON              0.000000
NAME_CLIENT_TYPE                0.000000
NAME_GOODS_CATEGORY             0.000000
NAME_PORTFOLIO                  0.000000
NAME_PRODUCT_TYPE               0.000000
CHANNEL_TYPE                    0.000000
SELLERPLACE_AREA                0.000000
NAME_SELLER_INDUSTRY            0.000000
CNT_PAYMENT                    22.286366
NAME_YIELD_GROUP                0.000000
PRODUCT_COMBINATION             0.020716
dtype: float64


# boxplot for prev_df's annuity amount:

sns.boxplot(prev_df.AMT_ANNUITY)
prev_df.AMT_ANNUITY.fillna(prev_df.AMT_ANNUITY.median(), inplace=True)


# boxplot for prev_df's AMT_GOODS_PRICE:

sns.boxplot(prev_df.AMT_GOODS_PRICE)
prev_df.AMT_GOODS_PRICE.fillna(prev_df.AMT_GOODS_PRICE.median(), inplace=True)


# boxplot for prev_df's CNT_PAYMENT:

sns.boxplot(prev_df.CNT_PAYMENT)
print(prev_df.CNT_PAYMENT.value_counts().head())
prev_df.CNT_PAYMENT.fillna(prev_df.CNT_PAYMENT.median(), inplace=True)

12.0    323049
6.0     190461
0.0     144985
10.0    141851
24.0    137764
Name: CNT_PAYMENT, dtype: int64


# valuecount of PRODUCT_COMBINATION:

prev_df.PRODUCT_COMBINATION.value_counts()

Cash                              285990
POS household with interest       263622
POS mobile with interest          220670
Cash X-Sell: middle               143883
Cash X-Sell: low                  130248
Card Street                       112582
POS industry with interest         98833
POS household without interest     82908
Card X-Sell                        80582
Cash Street: high                  59639
Cash X-Sell: high                  59301
Cash Street: middle                34658
Cash Street: low                   33834
POS mobile without interest        24082
POS other with interest            23879
POS industry without interest      12602
POS others without interest         2555
Name: PRODUCT_COMBINATION, dtype: int64


# filling missing values with mode:

prev_df.PRODUCT_COMBINATION.fillna(prev_df.PRODUCT_COMBINATION.mode()[0], inplace=True)


# Plotting a count plot on TARGET's object columns:

c_plot=['NAME_CONTRACT_TYPE','NAME_PAYMENT_TYPE','NAME_CLIENT_TYPE','NAME_CASH_LOAN_PURPOSE']
for i in c_plot:
    plt.style.use('ggplot')
    plt.figure(figsize = [18,8])
    plt.title(f'{i} Distribution of clients')
    sns.countplot(data=prev_df, x =i, hue='NAME_CONTRACT_STATUS', order = sorted(prev_df[i].unique(), reverse = True))
    plt.xticks(rotation = 90)


# Checking contract status vs name client type aggregating over application amount:

res=pd.pivot_table(data=prev_df, index="NAME_CONTRACT_STATUS",columns='NAME_CLIENT_TYPE',values='AMT_APPLICATION')
plt.figure(figsize=(15,8))
sns.heatmap(res, annot=True,cmap='Greens', fmt="g")
plt.show()


# Checking contract status vs name client type aggregating over AMOUNT GOOD PRICE:

res=pd.pivot_table(data=prev_df, index="NAME_CONTRACT_STATUS",columns='NAME_CLIENT_TYPE',values='AMT_GOODS_PRICE')
plt.figure(figsize=(15,8))
sns.heatmap(res, annot=True,cmap='Greens', fmt="g")
plt.show()

		count	mean	std	min	25%	50%	75%	max
NAME_EDUCATION_TYPE	CODE_GENDER
Academic degree	F	3.0	950245.500000	504711.375348	544491.0	667660.5	790830.0	1153122.750	1515415.5
Higher education	F	2438.0	648500.581624	410415.703440	47970.0	323595.0	545040.0	877500.000	2687355.0
Higher education	M	1571.0	642070.944940	409695.934841	45000.0	329202.0	545040.0	832797.000	2695500.0
Incomplete higher	F	504.0	520150.348214	352227.493127	91692.0	269550.0	450000.0	675000.000	2695500.0
Incomplete higher	M	368.0	554723.816576	347843.706372	74628.0	284400.0	497520.0	699976.125	2013840.0

	SK_ID_PREV	SK_ID_CURR	AMT_ANNUITY	AMT_APPLICATION	AMT_CREDIT	AMT_DOWN_PAYMENT	AMT_GOODS_PRICE	HOUR_APPR_PROCESS_START	NFLAG_LAST_APPL_IN_DAY	RATE_DOWN_PAYMENT	...	RATE_INTEREST_PRIVILEGED	DAYS_DECISION	SELLERPLACE_AREA	CNT_PAYMENT	DAYS_FIRST_DRAWING	DAYS_FIRST_DUE	DAYS_LAST_DUE_1ST_VERSION	DAYS_LAST_DUE	DAYS_TERMINATION	NFLAG_INSURED_ON_APPROVAL
count	1.670214e+06	1.670214e+06	1.297979e+06	1.670214e+06	1.670213e+06	7.743700e+05	1.284699e+06	1.670214e+06	1.670214e+06	774370.000000	...	5951.000000	1.670214e+06	1.670214e+06	1.297984e+06	997149.000000	997149.000000	997149.000000	997149.000000	997149.000000	997149.000000
mean	1.923089e+06	2.783572e+05	1.595512e+04	1.752339e+05	1.961140e+05	6.697402e+03	2.278473e+05	1.248418e+01	9.964675e-01	0.079637	...	0.773503	-8.806797e+02	3.139511e+02	1.605408e+01	342209.855039	13826.269337	33767.774054	76582.403064	81992.343838	0.332570
std	5.325980e+05	1.028148e+05	1.478214e+04	2.927798e+05	3.185746e+05	2.092150e+04	3.153966e+05	3.334028e+00	5.932963e-02	0.107823	...	0.100879	7.790997e+02	7.127443e+03	1.456729e+01	88916.115834	72444.869708	106857.034789	149647.415123	153303.516729	0.471134
min	1.000001e+06	1.000010e+05	0.000000e+00	0.000000e+00	0.000000e+00	-9.000000e-01	0.000000e+00	0.000000e+00	0.000000e+00	-0.000015	...	0.373150	-2.922000e+03	-1.000000e+00	0.000000e+00	-2922.000000	-2892.000000	-2801.000000	-2889.000000	-2874.000000	0.000000
25%	1.461857e+06	1.893290e+05	6.321780e+03	1.872000e+04	2.416050e+04	0.000000e+00	5.084100e+04	1.000000e+01	1.000000e+00	0.000000	...	0.715645	-1.300000e+03	-1.000000e+00	6.000000e+00	365243.000000	-1628.000000	-1242.000000	-1314.000000	-1270.000000	0.000000
50%	1.923110e+06	2.787145e+05	1.125000e+04	7.104600e+04	8.054100e+04	1.638000e+03	1.123200e+05	1.200000e+01	1.000000e+00	0.051605	...	0.835095	-5.810000e+02	3.000000e+00	1.200000e+01	365243.000000	-831.000000	-361.000000	-537.000000	-499.000000	0.000000
75%	2.384280e+06	3.675140e+05	2.065842e+04	1.803600e+05	2.164185e+05	7.740000e+03	2.340000e+05	1.500000e+01	1.000000e+00	0.108909	...	0.852537	-2.800000e+02	8.200000e+01	2.400000e+01	365243.000000	-411.000000	129.000000	-74.000000	-44.000000	1.000000
max	2.845382e+06	4.562550e+05	4.180581e+05	6.905160e+06	6.905160e+06	3.060045e+06	6.905160e+06	2.300000e+01	1.000000e+00	1.000000	...	1.000000	-1.000000e+00	4.000000e+06	8.400000e+01	365243.000000	365243.000000	365243.000000	365243.000000	365243.000000	1.000000

EDA Project : Credit Risk Analysis¶

Import all required libraries:¶

Loading Data:¶

Understanding loaded data:¶

Checking missing values in app_df:¶

Handling Missing Values:¶

Now, app_df has 307511 rows and 81 columns.¶

Handling Outlier values and NULL values¶

AMT_ANNUITY Imputation¶

AMT_GOODS_PRICE Imputation¶

NAME_TYPE_SUITE Imputation¶

CNT_FAM_MEMBERS Imputation¶

OCCUPATION_TYPE Imputation¶

EXT_SOURCE_2 Imputation¶

AMT_CREDIT Imputation¶

EXT_SOURCE_3 Imputation¶

Number of enquiries to Credit Bureau Imputation¶

OBS_30_CNT_SOCIAL_CIRCLE Imputation¶

Inspecting incorrect/unknown data values¶

inspecting NAME_FAMILY_STATUS column¶

inspecting DAYS_EMPLOYED column¶

inspecting DAYS_REGISTRATION column¶

inspecting DAYS_ID_PUBLISH column¶

inspecting DAYS_LAST_PHONE_CHANGE column¶

Data Analysis¶

Creating new dataframe with TARGET value¶

Univariate analysis¶

Observations:¶

Analysis of numeric columns¶

Observations:¶

Analysis of numeric columns by Binning¶

Bivariate/Multivariate analysis¶

Top Correlation¶

Previous-Application analysis¶

Reserch Summary:¶

Chances of client havind payment difficulty¶

	SK_ID_CURR	TARGET	NAME_CONTRACT_TYPE	CODE_GENDER	FLAG_OWN_CAR	FLAG_OWN_REALTY	CNT_CHILDREN	AMT_INCOME_TOTAL	AMT_CREDIT	AMT_ANNUITY	...	FLAG_DOCUMENT_18	FLAG_DOCUMENT_19	FLAG_DOCUMENT_20	FLAG_DOCUMENT_21	AMT_REQ_CREDIT_BUREAU_HOUR	AMT_REQ_CREDIT_BUREAU_DAY	AMT_REQ_CREDIT_BUREAU_WEEK	AMT_REQ_CREDIT_BUREAU_MON	AMT_REQ_CREDIT_BUREAU_QRT	AMT_REQ_CREDIT_BUREAU_YEAR
0	100002	1	Cash loans	M	N	Y	0	202500.0	406597.5	24700.5	...	0	0	0	0	0.0	0.0	0.0	0.0	0.0	1.0
1	100003	0	Cash loans	F	N	N	0	270000.0	1293502.5	35698.5	...	0	0	0	0	0.0	0.0	0.0	0.0	0.0	0.0
2	100004	0	Revolving loans	M	Y	Y	0	67500.0	135000.0	6750.0	...	0	0	0	0	0.0	0.0	0.0	0.0	0.0	0.0
3	100006	0	Cash loans	F	N	Y	0	135000.0	312682.5	29686.5	...	0	0	0	0	NaN	NaN	NaN	NaN	NaN	NaN
4	100007	0	Cash loans	M	N	Y	0	121500.0	513000.0	21865.5	...	0	0	0	0	0.0	0.0	0.0	0.0	0.0	0.0
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
307506	456251	0	Cash loans	M	N	N	0	157500.0	254700.0	27558.0	...	0	0	0	0	NaN	NaN	NaN	NaN	NaN	NaN
307507	456252	0	Cash loans	F	N	Y	0	72000.0	269550.0	12001.5	...	0	0	0	0	NaN	NaN	NaN	NaN	NaN	NaN
307508	456253	0	Cash loans	F	N	Y	0	153000.0	677664.0	29979.0	...	0	0	0	0	1.0	0.0	0.0	1.0	0.0	1.0
307509	456254	1	Cash loans	F	N	Y	0	171000.0	370107.0	20205.0	...	0	0	0	0	0.0	0.0	0.0	0.0	0.0	0.0
307510	456255	0	Cash loans	F	N	N	0	157500.0	675000.0	49117.5	...	0	0	0	0	0.0	0.0	0.0	2.0	0.0	1.0

	SK_ID_PREV	SK_ID_CURR	NAME_CONTRACT_TYPE	AMT_ANNUITY	AMT_APPLICATION	AMT_CREDIT	AMT_DOWN_PAYMENT	AMT_GOODS_PRICE	WEEKDAY_APPR_PROCESS_START	HOUR_APPR_PROCESS_START	...	NAME_SELLER_INDUSTRY	CNT_PAYMENT	NAME_YIELD_GROUP	PRODUCT_COMBINATION	DAYS_FIRST_DRAWING	DAYS_FIRST_DUE	DAYS_LAST_DUE_1ST_VERSION	DAYS_LAST_DUE	DAYS_TERMINATION	NFLAG_INSURED_ON_APPROVAL
0	2030495	271877	Consumer loans	1730.430	17145.0	17145.0	0.0	17145.0	SATURDAY	15	...	Connectivity	12.0	middle	POS mobile with interest	365243.0	-42.0	300.0	-42.0	-37.0	0.0
1	2802425	108129	Cash loans	25188.615	607500.0	679671.0	NaN	607500.0	THURSDAY	11	...	XNA	36.0	low_action	Cash X-Sell: low	365243.0	-134.0	916.0	365243.0	365243.0	1.0
2	2523466	122040	Cash loans	15060.735	112500.0	136444.5	NaN	112500.0	TUESDAY	11	...	XNA	12.0	high	Cash X-Sell: high	365243.0	-271.0	59.0	365243.0	365243.0	1.0
3	2819243	176158	Cash loans	47041.335	450000.0	470790.0	NaN	450000.0	MONDAY	7	...	XNA	12.0	middle	Cash X-Sell: middle	365243.0	-482.0	-152.0	-182.0	-177.0	1.0
4	1784265	202054	Cash loans	31924.395	337500.0	404055.0	NaN	337500.0	THURSDAY	9	...	XNA	24.0	high	Cash Street: high	NaN	NaN	NaN	NaN	NaN	NaN
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
1670209	2300464	352015	Consumer loans	14704.290	267295.5	311400.0	0.0	267295.5	WEDNESDAY	12	...	Furniture	30.0	low_normal	POS industry with interest	365243.0	-508.0	362.0	-358.0	-351.0	0.0
1670210	2357031	334635	Consumer loans	6622.020	87750.0	64291.5	29250.0	87750.0	TUESDAY	15	...	Furniture	12.0	middle	POS industry with interest	365243.0	-1604.0	-1274.0	-1304.0	-1297.0	0.0
1670211	2659632	249544	Consumer loans	11520.855	105237.0	102523.5	10525.5	105237.0	MONDAY	12	...	Consumer electronics	10.0	low_normal	POS household with interest	365243.0	-1457.0	-1187.0	-1187.0	-1181.0	0.0
1670212	2785582	400317	Cash loans	18821.520	180000.0	191880.0	NaN	180000.0	WEDNESDAY	9	...	XNA	12.0	low_normal	Cash X-Sell: low	365243.0	-1155.0	-825.0	-825.0	-817.0	1.0
1670213	2418762	261212	Cash loans	16431.300	360000.0	360000.0	NaN	360000.0	SUNDAY	10	...	XNA	48.0	middle	Cash X-Sell: middle	365243.0	-1163.0	247.0	-443.0	-423.0	0.0

	SK_ID_CURR	TARGET	CNT_CHILDREN	AMT_INCOME_TOTAL	AMT_CREDIT	AMT_ANNUITY	AMT_GOODS_PRICE	REGION_POPULATION_RELATIVE	DAYS_BIRTH	DAYS_EMPLOYED	...	FLAG_DOCUMENT_18	FLAG_DOCUMENT_19	FLAG_DOCUMENT_20	FLAG_DOCUMENT_21	AMT_REQ_CREDIT_BUREAU_HOUR	AMT_REQ_CREDIT_BUREAU_DAY	AMT_REQ_CREDIT_BUREAU_WEEK	AMT_REQ_CREDIT_BUREAU_MON	AMT_REQ_CREDIT_BUREAU_QRT	AMT_REQ_CREDIT_BUREAU_YEAR
count	307511.000000	307511.000000	307511.000000	3.075110e+05	3.075110e+05	307499.000000	3.072330e+05	307511.000000	307511.000000	307511.000000	...	307511.000000	307511.000000	307511.000000	307511.000000	265992.000000	265992.000000	265992.000000	265992.000000	265992.000000	265992.000000
mean	278180.518577	0.080729	0.417052	1.687979e+05	5.990260e+05	27108.573909	5.383962e+05	0.020868	-16036.995067	63815.045904	...	0.008130	0.000595	0.000507	0.000335	0.006402	0.007000	0.034362	0.267395	0.265474	1.899974
std	102790.175348	0.272419	0.722121	2.371231e+05	4.024908e+05	14493.737315	3.694465e+05	0.013831	4363.988632	141275.766519	...	0.089798	0.024387	0.022518	0.018299	0.083849	0.110757	0.204685	0.916002	0.794056	1.869295
min	100002.000000	0.000000	0.000000	2.565000e+04	4.500000e+04	1615.500000	4.050000e+04	0.000290	-25229.000000	-17912.000000	...	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000
25%	189145.500000	0.000000	0.000000	1.125000e+05	2.700000e+05	16524.000000	2.385000e+05	0.010006	-19682.000000	-2760.000000	...	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000
50%	278202.000000	0.000000	0.000000	1.471500e+05	5.135310e+05	24903.000000	4.500000e+05	0.018850	-15750.000000	-1213.000000	...	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	1.000000
75%	367142.500000	0.000000	1.000000	2.025000e+05	8.086500e+05	34596.000000	6.795000e+05	0.028663	-12413.000000	-289.000000	...	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	3.000000
max	456255.000000	1.000000	19.000000	1.170000e+08	4.050000e+06	258025.500000	4.050000e+06	0.072508	-7489.000000	365243.000000	...	1.000000	1.000000	1.000000	1.000000	4.000000	9.000000	8.000000	27.000000	261.000000	25.000000

	SK_ID_CURR	NAME_CONTRACT_TYPE	CODE_GENDER	FLAG_OWN_CAR	FLAG_OWN_REALTY	AMT_INCOME_TOTAL	AMT_CREDIT	AMT_ANNUITY	...	AMT_REQ_CREDIT_BUREAU_HOUR	AMT_REQ_CREDIT_BUREAU_DAY	AMT_REQ_CREDIT_BUREAU_WEEK	AMT_REQ_CREDIT_BUREAU_MON	AMT_REQ_CREDIT_BUREAU_QRT	AMT_REQ_CREDIT_BUREAU_YEAR
47531	155054	Cash loans	M	N	N	180000.0	450000.0	NaN	...	0.0	0.0	0.0	0.0	1.0	1.0
50035	157917	Cash loans	F	N	N	94500.0	450000.0	NaN	...	0.0	0.0	0.0	1.0	0.0	1.0
51594	159744	Cash loans	F	N	N	202500.0	539100.0	NaN	...	0.0	0.0	0.0	1.0	0.0	1.0
55025	163757	Cash loans	F	N	N	162000.0	296280.0	NaN	...	0.0	0.0	0.0	1.0	0.0	4.0
59934	169487	Cash loans	M	Y	N	202500.0	360000.0	NaN	...	0.0	0.0	1.0	0.0	0.0	6.0
75873	187985	Cash loans	M	Y	N	144000.0	219249.0	NaN	...	0.0	0.0	0.0	0.0	0.0	11.0
89343	203726	Cash loans	F	Y	N	90000.0	157500.0	NaN	...	0.0	0.0	0.0	2.0	0.0	0.0
123872	243648	Cash loans	F	N	Y	202500.0	929088.0	NaN	...	0.0	0.0	0.0	0.0	1.0	5.0
207186	340147	Cash loans	M	N	N	171000.0	486000.0	NaN	...	0.0	0.0	1.0	1.0	0.0	2.0
227939	364022	Cash loans	F	N	Y	315000.0	628069.5	NaN	...	0.0	0.0	1.0	0.0	0.0	2.0
239329	377174	Cash loans	F	N	Y	157500.0	792000.0	NaN	...	0.0	0.0	0.0	0.0	1.0	1.0
241835	379997	Cash loans	F	N	N	315000.0	1483231.5	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN

	SK_ID_CURR	TARGET	NAME_CONTRACT_TYPE	CODE_GENDER	FLAG_OWN_CAR	FLAG_OWN_REALTY	CNT_CHILDREN	AMT_INCOME_TOTAL	AMT_CREDIT	AMT_ANNUITY	...	FLAG_DOCUMENT_18	FLAG_DOCUMENT_19	FLAG_DOCUMENT_20	FLAG_DOCUMENT_21	AMT_REQ_CREDIT_BUREAU_HOUR	AMT_REQ_CREDIT_BUREAU_DAY	AMT_REQ_CREDIT_BUREAU_WEEK	AMT_REQ_CREDIT_BUREAU_MON	AMT_REQ_CREDIT_BUREAU_QRT	AMT_REQ_CREDIT_BUREAU_YEAR
724	100837	0	Revolving loans	F	N	Y	2	45000.0	135000.0	6750.0	...	0	0	0	0	NaN	NaN	NaN	NaN	NaN	NaN
5937	106955	0	Revolving loans	F	N	N	0	157500.0	450000.0	22500.0	...	0	0	0	0	0.0	0.0	0.0	0.0	0.0	2.0
6425	107494	0	Revolving loans	F	N	N	0	67500.0	202500.0	10125.0	...	0	0	0	0	NaN	NaN	NaN	NaN	NaN	NaN
6703	107822	0	Revolving loans	F	N	N	1	121500.0	180000.0	9000.0	...	0	0	0	0	0.0	0.0	0.0	0.0	0.0	0.0
7647	108913	0	Revolving loans	M	N	Y	0	180000.0	450000.0	22500.0	...	0	0	0	0	0.0	0.0	0.0	0.0	0.0	3.0
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
304678	452992	0	Revolving loans	F	N	Y	2	67500.0	202500.0	10125.0	...	0	0	0	0	0.0	0.0	0.0	0.0	0.0	1.0
304784	453120	0	Revolving loans	M	N	Y	1	112500.0	270000.0	13500.0	...	0	0	0	0	0.0	0.0	0.0	0.0	0.0	0.0
305833	454341	0	Revolving loans	F	N	Y	0	126000.0	270000.0	13500.0	...	0	0	0	0	0.0	0.0	0.0	0.0	0.0	2.0
306126	454683	0	Revolving loans	M	N	Y	0	135000.0	270000.0	13500.0	...	0	0	0	0	0.0	0.0	0.0	0.0	0.0	3.0
306273	454852	0	Revolving loans	M	N	Y	2	67500.0	202500.0	10125.0	...	0	0	0	0	0.0	0.0	0.0	1.0	0.0	0.0

	SK_ID_CURR	TARGET	NAME_CONTRACT_TYPE	CODE_GENDER	FLAG_OWN_CAR	FLAG_OWN_REALTY	CNT_CHILDREN	AMT_INCOME_TOTAL	AMT_CREDIT	AMT_ANNUITY	...	FLAG_DOCUMENT_18	FLAG_DOCUMENT_19	FLAG_DOCUMENT_20	FLAG_DOCUMENT_21	AMT_REQ_CREDIT_BUREAU_HOUR	AMT_REQ_CREDIT_BUREAU_DAY	AMT_REQ_CREDIT_BUREAU_WEEK	AMT_REQ_CREDIT_BUREAU_MON	AMT_REQ_CREDIT_BUREAU_QRT	AMT_REQ_CREDIT_BUREAU_YEAR
41982	148605	0	Revolving loans	M	N	Y	0	450000.0	675000.0	33750.0	...	0	0	0	0	NaN	NaN	NaN	NaN	NaN	NaN
187348	317181	0	Revolving loans	F	N	Y	0	202500.0	585000.0	29250.0	...	0	0	0	0	0.0	0.0	0.0	0.0	0.0	1.0

	SK_ID_CURR	NAME_CONTRACT_TYPE	CODE_GENDER	FLAG_OWN_CAR	FLAG_OWN_REALTY	CNT_CHILDREN	AMT_INCOME_TOTAL	AMT_CREDIT	AMT_ANNUITY	...	AMT_REQ_CREDIT_BUREAU_MON	AMT_REQ_CREDIT_BUREAU_QRT	AMT_REQ_CREDIT_BUREAU_YEAR
35657	141289	Revolving loans	XNA	Y	Y	0	207000.0	382500.0	19125.0	...	0.0	0.0	1.0
38566	144669	Revolving loans	XNA	N	Y	2	157500.0	270000.0	13500.0	...	3.0	0.0	4.0
83382	196708	Revolving loans	XNA	N	Y	1	135000.0	405000.0	20250.0	...	0.0	0.0	3.0
189640	319880	Revolving loans	XNA	Y	Y	0	247500.0	540000.0	27000.0	...	0.0	1.0	6.0